﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;
using System.Data;

namespace Bewise.SmartIndexManagerLT
{
    [DtsTask
    (
    // Nom affiché sur la ToolBox
    DisplayName = "SmartIndexManagerLT_DisplayName",
    Description = "SmartIndexManagerLT_Description",
    RequiredProductLevel = DTSProductLevel.None,
    TaskContact = "SmartIndexManagerLT_Contact"
    )]
    public class SmartIndexManagerLT : Task
    {
        // déclaration de notre Propriété publique SqlServerConnection qui va nous permettre de stocker une chaine de
        // caractère qui correspondra au nom du Connection Manager à utiliser.
        public string SqlServerConnection { get; set; }

        public string Query { get; set; }

        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            // affichage des informations du ConnectionManager dont le nom est stocké dans la propriété "SqlServerConnection"
            //MessageBox.Show(ConnectionManagerDescription(connections[SqlServerConnection]));

            using(var sqlConnection = new SqlConnection(connections[SqlServerConnection].ConnectionString))
            {
                if(sqlConnection.State != ConnectionState.Open)
                    sqlConnection.Open();

                using(var sqlCommand = new SqlCommand(Query, sqlConnection))
                {
                    using (var reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        var sb = new StringBuilder();
                        while (reader.Read())
                        {
                            sb.AppendLine(reader[0].ToString());
                        }
                        MessageBox.Show(sb.ToString());
                    }
                }
            }

            return DTSExecResult.Success;
        }

        public string ConnectionManagerDescription(ConnectionManager cm)
        {
            return string.Format("Name = {0}, ConnectionString = {1}", cm.Name, cm.ConnectionString);
        }
    }
}